﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using Aspose.Words;
using System.Windows.Forms;
using System.IO;
using HiStaff.Util.Enum;
using System.Data;

namespace HiStaff.Util
{
    public class Export
    {
        public static bool Export2ExcelFromDatabase(string ReportPath, DataSet dsSource, DataTable dtVariable)
        {
            return Export2ExcelFromDatabase(ReportPath, dsSource, dtVariable, ReportEnCode.Unicode);
        }
        public static bool Export2ExcelFromDatabase(string ReportPath, DataSet dsSource, DataTable dtVariable, ReportEnCode enCode)
        {
            try
            {
                if (enCode == ReportEnCode.VNI)
                {
                    if (dtVariable != null)
                    {
                        foreach (DataRow dr in dtVariable.Rows)
                        {
                            foreach (DataColumn dc in dtVariable.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }

                    foreach (DataTable dt in dsSource.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }
                else if (enCode == ReportEnCode.TCVN3)
                {
                    if (dtVariable != null)
                    {
                        foreach (DataRow dr in dtVariable.Rows)
                        {
                            foreach (DataColumn dc in dtVariable.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }

                    foreach (DataTable dt in dsSource.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }

                string strRoot = AppDomain.CurrentDomain.BaseDirectory;

                Aspose.Cells.License l = new Aspose.Cells.License();
                string strLicense = strRoot + "Librarys\\Aspose\\Aspose.Cells.lic";
                l.SetLicense(strLicense);

                string filePath = AppDomain.CurrentDomain.BaseDirectory + "\\" + ReportPath;
                //getReportPath(sReportID)

                if (File.Exists(filePath))
                {
                    WorkbookDesigner designer = new WorkbookDesigner();
                    designer.Open(filePath);

                    designer.SetDataSource(dsSource);

                    if (dtVariable != null)
                    {
                        int intCols = dtVariable.Columns.Count;
                        for (int i = 0; i <= intCols - 1; i++)
                        {
                            designer.SetDataSource(dtVariable.Columns[i].ColumnName.ToString(), dtVariable.Rows[0].ItemArray[i].ToString());
                        }
                    }

                    designer.Process();
                    //Save the excel file
                    FileFormatType Format = new Aspose.Cells.FileFormatType();
                    FileInfo fi = new FileInfo(filePath);
                    if (fi.Extension == ".xlsx")
                    {
                        Format = Aspose.Cells.FileFormatType.Xlsx;
                    }
                    else
                    {
                        Format = Aspose.Cells.FileFormatType.Excel97To2003;
                    }
                    SaveFileDialog savedlg = new SaveFileDialog();
                    savedlg.Filter = string.Format("*{0}|*{0}", fi.Extension);
                    savedlg.ShowDialog();
                    string fileAttachInfo = savedlg.FileName;
                    if (!string.IsNullOrEmpty(fileAttachInfo))
                    {
                        designer.Save(fileAttachInfo, Format);
                        System.Diagnostics.Process.Start(fileAttachInfo);
                    }
                }
                else
                {
                    MessageBox.Show("Xin vui lòng kiểm tra tập tin template không tồn tại...", "Xuất báo cáo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "Export2ExcelFromDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log.Instance.WriteExceptionLog(ex, "[Export2ExcelFromDatabase]");
                return false;
                //Throw ex
            }
            return true;
        }

        public static bool Export2ExcelDynamicColumn(string ReportPath, DataTable dtHeader, DataSet dsContent, DataTable DataVariable)
        {
            return Export2ExcelDynamicColumn(ReportPath, dtHeader, dsContent, DataVariable, ReportEnCode.Unicode);
        }
        public static bool Export2ExcelDynamicColumn(string ReportPath, DataTable dtHeader, DataSet dsContent, DataTable DataVariable, ReportEnCode enCode)
        {
            try
            {
                if (enCode == ReportEnCode.VNI)
                {
                    if (DataVariable != null)
                    {
                        foreach (DataRow dr in DataVariable.Rows)
                        {
                            foreach (DataColumn dc in DataVariable.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                    foreach (DataRow dr in dtHeader.Rows)
                    {
                        foreach (DataColumn dc in dtHeader.Columns)
                        {
                            if (dc.DataType == typeof(string))
                            {
                                dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                            }
                        }
                    }
                    foreach (DataTable dt in dsContent.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }
                else if (enCode == ReportEnCode.TCVN3)
                {
                    if (DataVariable != null)
                    {
                        foreach (DataRow dr in DataVariable.Rows)
                        {
                            foreach (DataColumn dc in DataVariable.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                    foreach (DataRow dr in dtHeader.Rows)
                    {
                        foreach (DataColumn dc in dtHeader.Columns)
                        {
                            if (dc.DataType == typeof(string))
                            {
                                dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                            }
                        }
                    }
                    foreach (DataTable dt in dsContent.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }

                String strRoot = AppDomain.CurrentDomain.BaseDirectory;

                Aspose.Cells.License l = new Aspose.Cells.License();
                String strLicense = strRoot + "Librarys\\Aspose\\Aspose.Cells.lic";
                l.SetLicense(strLicense);

                String filePath = AppDomain.CurrentDomain.BaseDirectory + "\\" + ReportPath;

                if (File.Exists(filePath))
                {
                    WorkbookDesigner designer = new WorkbookDesigner();
                    designer.Open(filePath);

                    designer.SetDataSource(dtHeader);

                    if (DataVariable != null)
                    {
                        if (DataVariable.Rows.Count > 0)
                        {
                            foreach (DataColumn dc in DataVariable.Columns)
                            {
                                designer.SetDataSource(dc.ColumnName, DataVariable.Rows[0][dc.ColumnName]);
                            }
                        }
                    }

                    designer.Process();

                    //Save the excel file
                    FileFormatType Format = new Aspose.Cells.FileFormatType();
                    FileInfo fi = new FileInfo(filePath);
                    if (fi.Extension == ".xlsx")
                    {
                        Format = Aspose.Cells.FileFormatType.Xlsx;
                    }
                    else
                    {
                        Format = Aspose.Cells.FileFormatType.Excel97To2003;
                    }
                    //'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    string fileAttachInfo = AppDomain.CurrentDomain.BaseDirectory + "\\export\\";
                    if (!System.IO.Directory.Exists(fileAttachInfo))
                    {
                        System.IO.Directory.CreateDirectory(fileAttachInfo);
                    }
                    String Template = AppDomain.CurrentDomain.BaseDirectory + "\\export\\TemplateExport.xls";
                    designer.Save(Template, Format);

                    designer = new WorkbookDesigner();
                    designer.Open(Template);
                    designer.SetDataSource(dsContent);

                    designer.Process();

                    SaveFileDialog savedlg = new SaveFileDialog();
                    savedlg.Filter = string.Format("*{0}|*{0}", fi.Extension);
                    savedlg.ShowDialog();
                    fileAttachInfo = savedlg.FileName;
                    if (!string.IsNullOrEmpty(fileAttachInfo))
                    {
                        designer.Save(fileAttachInfo, Format);
                        System.Diagnostics.Process.Start(fileAttachInfo);
                    }
                }
                else
                {
                    MessageBox.Show("Xin vui lòng kiểm tra tập tin template không tồn tại...", "Xuất báo cáo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "export2ExcelDynamicColumn", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log.Instance.WriteExceptionLog(ex, "[export2ExcelDynamicColumn]");
                return false;
            }
            return true;
        }

        public static bool Export2WordFromDatabase(string ReportPath, DataTable dtContent)
        {
            return Export2WordFromDatabase(ReportPath, dtContent, ReportEnCode.Unicode);
        }
        public static bool Export2WordFromDatabase(string ReportPath, DataTable dtContent, ReportEnCode enCode)
        {
            try
            {
                if (enCode == ReportEnCode.VNI)
                {
                    foreach (DataRow dr in dtContent.Rows)
                    {
                        foreach (DataColumn dc in dtContent.Columns)
                        {
                            if (dc.DataType == typeof(string))
                            {
                                dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                            }
                        }
                    }
                }
                else if (enCode == ReportEnCode.TCVN3)
                {
                    foreach (DataRow dr in dtContent.Rows)
                    {
                        foreach (DataColumn dc in dtContent.Columns)
                        {
                            if (dc.DataType == typeof(string))
                            {
                                dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                            }
                        }
                    }
                }
                string strRoot = AppDomain.CurrentDomain.BaseDirectory ;

                Aspose.Words.License l = new Aspose.Words.License();
                string strLicense = strRoot + "Librarys\\Aspose\\Aspose.Words.lic";
                l.SetLicense(strLicense);

                string filePath = AppDomain.CurrentDomain.BaseDirectory  + ReportPath;


                if (File.Exists(filePath))
                {
                    Aspose.Words.Document doc = new Aspose.Words.Document(filePath);
                    doc.MailMerge.Execute(dtContent);
                    Aspose.Words.SaveFormat Format = new Aspose.Words.SaveFormat();
                    FileInfo fileInfo = new FileInfo(filePath);

                    if (fileInfo.Extension == ".docx")
                    {
                        Format = Aspose.Words.SaveFormat.Docx;
                    }
                    else
                    {
                        Format = Aspose.Words.SaveFormat.Doc;
                    }
                    SaveFileDialog savedlg = new SaveFileDialog();
                    savedlg.Filter = string.Format("*{0}|*{0}", fileInfo.Extension);
                    savedlg.ShowDialog();
                    string fileAttachInfo = savedlg.FileName;
                    if (!string.IsNullOrEmpty(fileAttachInfo))
                    {
                        doc.Save(fileAttachInfo, Format);
                        System.Diagnostics.Process.Start(fileAttachInfo);
                    }
                }
                else
                {
                    MessageBox.Show("Xin vui lòng kiểm tra tập tin template không tồn tại...", "Xuất báo cáo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "export2ExcelDynamicColumn", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log.Instance.WriteExceptionLog(ex, "[export2ExcelDynamicColumn]");
                return false;
            }
            return true;
        }

        public static bool Export2WordFromDatabase(string ReportPath, DataTable dtContent, DataSet dsExplain)
        {
            return Export2WordFromDatabase(ReportPath, dtContent, dsExplain, ReportEnCode.Unicode);
        }
        public static bool Export2WordFromDatabase(string ReportPath, DataTable dtContent, DataSet dsExplain, ReportEnCode enCode)
        {
            try
            {
                if (enCode == ReportEnCode.VNI)
                {
                    if (dtContent != null)
                    {
                        foreach (DataRow dr in dtContent.Rows)
                        {
                            foreach (DataColumn dc in dtContent.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                    foreach (DataTable dt in dsExplain.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToVni(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }
                else if (enCode == ReportEnCode.TCVN3)
                {
                    if (dtContent != null)
                    {
                        foreach (DataRow dr in dtContent.Rows)
                        {
                            foreach (DataColumn dc in dtContent.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                    foreach (DataTable dt in dsExplain.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == typeof(string))
                                {
                                    dr[dc.ColumnName] = ConvertHelper.UnicodeToTcvn3(dr[dc.ColumnName].ToString());
                                }
                            }
                        }
                    }
                }

                string strRoot = AppDomain.CurrentDomain.BaseDirectory;

                Aspose.Words.License l = new Aspose.Words.License();
                string strLicense = strRoot + "Librarys\\Aspose\\Aspose.Words.lic";
                l.SetLicense(strLicense);

                string filePath = AppDomain.CurrentDomain.BaseDirectory + "\\" + ReportPath;


                if (File.Exists(filePath))
                {
                    Aspose.Words.Document doc = new Aspose.Words.Document(filePath);
                    if (dsExplain != null && dsExplain.Tables.Count > 0)
                    {
                        doc.MailMerge.ExecuteWithRegions(dsExplain);

                    }
                    if (dtContent != null && dtContent.Rows.Count > 0)
                        doc.MailMerge.Execute(dtContent);
                    Aspose.Words.SaveFormat Format = new Aspose.Words.SaveFormat();
                    FileInfo fileInfo = new FileInfo(filePath);

                    if (fileInfo.Extension == ".docx")
                    {
                        Format = Aspose.Words.SaveFormat.Docx;
                    }
                    else
                    {
                        Format = Aspose.Words.SaveFormat.Doc;
                    }
                    SaveFileDialog savedlg = new SaveFileDialog();
                    savedlg.Filter = string.Format("*{0}|*{0}", fileInfo.Extension);
                    savedlg.ShowDialog();
                    string fileAttachInfo = savedlg.FileName;
                    if (!string.IsNullOrEmpty(fileAttachInfo))
                    {
                        doc.Save(fileAttachInfo, Format);
                        System.Diagnostics.Process.Start(fileAttachInfo);
                    }
                }
                else
                {
                    MessageBox.Show("Xin vui lòng kiểm tra tập tin template không tồn tại...", "Xuất báo cáo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "export2ExcelDynamicColumn", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log.Instance.WriteExceptionLog(ex, "[export2ExcelDynamicColumn]");
                return false;
            }
            return true;
        }
    }
}
